package data;

import java.sql.*;
import misc.Logger;

public class DatabaseGateway {
    private Connection con=null;

    //Initialise connection to database for future uses
    public void openConnection(String username, String password,
                                    String database_name)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"
            +database_name, username, password);
        }
        catch(SQLException e)
        {
            Logger.write("Unable to connect '"+database_name+
            "' database. Please check your details.",Logger.LogType.error);
        }
        catch(Exception e)
        {
            Logger.write("Java Driver required to communicate "+
            "with database cannot be located! Check Tomcat lib folder "+
            "for the driver JAR file.",Logger.LogType.error);
        }
    }

    //Close the active connection. Must open new connection if necessary
    public void closeConnection()
    {
        try
        {
            if(con!=null)
                con.close();
        }
        catch(SQLException e)
        {
            Logger.write("Unable to close the connection to the database.",
                            Logger.LogType.warning);
        }
    }

    //Start session to collect pending changes but NOT commit until explictly
    //commanded with the other method
    public void startTransaction() throws Exception
    {
        if(con==null)
            throw new Exception("Database connection is not initialised!");

        con.setAutoCommit(false);//Disable auto-execute on every query
    }

    //Commit all pending changes permenantly
    public void commitTransaction() throws Exception
    {
        if(con==null)
            throw new Exception("Database connection is not initialised!");

        con.commit();
        con.setAutoCommit(true);//Turn on back
    }

    //Retrieve rows of results
    public ResultSet executeQuery(String query) throws Exception
    {
        if(con==null)
            throw new Exception("Database connection is not initialised!");

        Statement stmt = con.createStatement();
        return stmt.executeQuery(query);
    }

    //Cause a change to database and get number of rows affacted
    public int executeUpdate(String query) throws Exception
    {
        if(con==null)
            throw new Exception("Database connection is not initialised!");

        Statement stmt = con.createStatement();
        return stmt.executeUpdate(query);
    }

    //Count the number of row of results
    public int executeCount(String query) throws Exception
    {
        int count=0;
        ResultSet rs = executeQuery(query);
        while(rs.next()) count++;
        return count;
    }
}
